﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using CommunitySystem.BO;
using CommunitySystemModel;

namespace CommunitySystem
{
    public class PeiXunBO
    {
        public ObservableCollection<PeiXunModel> GetPeiXunModel(int id)
        {
            ObservableCollection<PeiXunModel> rights = new ObservableCollection<PeiXunModel>();
            string sql = "";
            sql = string.Format(@"select row_number()over(order by StartTime desc) Sec,* from peixun where 1=1 ");
            if (id != 0)
                sql += " and id =" + id;
            sql += "  order by StartTime desc";

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PeiXunModel.FillEntity(dr));
            }
            return rights;
        }

        public ObservableCollection<PeiXunDetailModel> GetPeiXunDetailModel(string orgid, int id, int peixunid)
        {
            ObservableCollection<PeiXunDetailModel> rights = new ObservableCollection<PeiXunDetailModel>();
            string sql = "";
            sql = string.Format(@"SELECT row_number()over(order by p.ID ) Sec, pd.[ID],p.XianZhiPiaoShu
      ,pd.[PeiXunID]
      ,pd.[DingPiaoShu]
      ,pd.[OrgID]
      ,pd.[YiFaFang]
      ,pd.[YiFaFangPiaoShu],pd.FaFangFangShi
      ,CASE WHEN pd.OrgID = 0 THEN pd.orgName ELSE o.Name END OrgName,p.Name,p.ZhuJiangRen,p.JiangZuoDiDian,
p.JiangZuoShiJijan,p.PiaoShu,p.StartTime,p.JiangZuoType,
p.EndTime,p.YuPiao,p.Status FROM dbo.PeiXun p left join dbo.PeiXunDetail pd
ON pd.PeiXunID = p.ID left JOIN dbo.OrgForHR o ON pd.OrgID = o.ID where 1=1 ");
            if (orgid != "0") sql += " and orgid in ( " + orgid + ")";
            if (id != 0) sql += " and pd.id = " + id;
            if (peixunid != 0) sql += " and p.id = " + peixunid;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PeiXunDetailModel.FillEntity(dr));
            }
            return rights;
        }

        public ObservableCollection<PeiXunDetailModel> GetPeiXunDetailModel1(string orgid, int id, int peixunid)
        {
            ObservableCollection<PeiXunDetailModel> rights = new ObservableCollection<PeiXunDetailModel>();
            string sql = "";
            sql = string.Format(@"SELECT row_number()over(order by p.ID ) Sec, pd.[ID],p.XianZhiPiaoShu
      ,pd.[PeiXunID]
      ,pd.[DingPiaoShu]
      ,pd.[OrgID]
      ,pd.[YiFaFang]
      ,pd.[YiFaFangPiaoShu],pd.FaFangFangShi
      ,CASE WHEN pd.OrgID = 0 THEN pd.orgName ELSE o.Name END OrgName,p.Name,p.ZhuJiangRen,p.JiangZuoDiDian,
p.JiangZuoShiJijan,p.PiaoShu,p.StartTime,p.JiangZuoType,
p.EndTime,p.YuPiao,p.Status FROM dbo.PeiXun p inner join dbo.PeiXunDetail pd
ON pd.PeiXunID = p.ID left JOIN dbo.OrgForHR o ON pd.OrgID = o.ID where 1=1 ");
            if (orgid != "0") sql += " and orgid in ( " + orgid + ")";
            if (id != 0) sql += " and pd.id = " + id;
            if (peixunid != 0) sql += " and p.id = " + peixunid;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PeiXunDetailModel.FillEntity(dr));
            }
            return rights;
        }

        public int GetPeiXunDetailModel1(string orgid, int peixunid)
        {
            ObservableCollection<PeiXunDetailModel> rights = new ObservableCollection<PeiXunDetailModel>();
            string sql = "";
            sql = string.Format(@"SELECT [DingPiaoShu] FROM PeiXunDetail WHERE peixunid = {0} ", peixunid);
            if (orgid != "0") sql += " and orgid in ( " + orgid + ")";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            return 0;
        }

        public int GetPeiXunDetailModelForAdmin(int peixunid, string orgname)
        {
            ObservableCollection<PeiXunDetailModel> rights = new ObservableCollection<PeiXunDetailModel>();
            string sql = "";
            sql = string.Format(@"SELECT DingPiaoShu FROM dbo.PeiXunDetail WHERE PeiXunID = {0} AND OrgID = 0 AND orgName = '{1}' ", peixunid, orgname);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            return 0;
        }
        /// <summary>
        /// 看是否预订票数小于余票
        /// </summary>
        /// <param name="model"></param>
        /// <param name="piaoshu"></param>
        /// <returns></returns>
        public bool CanDingPiao(PeiXunDetailModel model, int piaoshu)
        {
            bool can = false;
            string sql = "";
            sql = string.Format(@"select YuPiao  from peixun where 1=1 ", piaoshu);
            if (model.PeiXunID != 0) sql += " and id = " + model.PeiXunID;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                if (Convert.ToInt32(ds.Tables[0].Rows[0][0]) < model.DingPiaoShu)
                    can = false;
                else can = true;

            }
            return can;
        }
        /// <summary>
        /// 看原定的票和刚定的票的和是否大于10张
        /// </summary>
        /// <param name="peixunid"></param>
        /// <param name="orgid"></param>
        /// <param name="yudingpiaoshu"></param>
        /// <param name="oldyudingpiaoshu"></param>
        /// <returns></returns>
        public bool CanDingPiao1(int peixunid, int orgid, int yudingpiaoshu, int oldyudingpiaoshu,int xianzhipiaoshu)
        {
            bool can = true;
            string sql = "";
            sql = string.Format(@"SELECT {2} + {3} FROM dbo.PeiXunDetail 
WHERE OrgID = {1} AND PeiXunID = {0} ", peixunid, orgid, yudingpiaoshu, oldyudingpiaoshu);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                if (Convert.ToInt32(ds.Tables[0].Rows[0][0]) > xianzhipiaoshu)
                    can = false;
                else can = true;

            }
            return can;
        }

        public bool CancelDingPiao(int peixundetailid)
        {
            string chasql =
            string.Format("SELECT DingPiaoShu FROM dbo.PeiXunDetail WHERE ID = {0} ", peixundetailid);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(chasql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                string sql = "";
                sql = string.Format(@"UPDATE dbo.PeiXun SET YuPiao = YuPiao + 
(SELECT DingPiaoShu FROM dbo.PeiXunDetail WHERE ID = {0} )
WHERE dbo.PeiXun.ID = (SELECT PeiXunID FROM dbo.PeiXunDetail WHERE ID = {0} );
DELETE FROM dbo.PeiXunDetail WHERE ID = {0} ", peixundetailid);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                return true;

            }
            else return false;

        }


        public bool AddPeiXunDetailModel(PeiXunDetailModel item)
        {
            if (item.OrgID != 0)
            {
                string sql = "";
                string chasql = string.Format(@"SELECT ID FROM dbo.PeiXunDetail WHERE PeiXunID = {0} AND OrgID = {1}", item.PeiXunID, item.OrgID);
                DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(chasql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    sql = string.Format(@"UPDATE PeiXunDetail
   SET [DingPiaoShu] = DingPiaoShu + {1},FaFangFangShi = '{3}',YiFaFang = 0
 WHERE id = {2};update PeiXun set YuPiao = CASE WHEN YuPiao - {1} <= 0 THEN 0 ELSE YuPiao - {1}  END where id = {0};
UPDATE dbo.PeiXun SET Status = 1 WHERE YuPiao = 0 AND ID = {0}", item.PeiXunID, item.DingPiaoShu, Convert.ToInt32(ds.Tables[0].Rows[0][0]), item.FaFangFangShi);

                }
                else
                {
                    sql = string.Format(@"INSERT INTO PeiXunDetail
           ([PeiXunID]
           ,[DingPiaoShu]
           ,[OrgID],FaFangFangShi)
     VALUES
           ({0}
           ,{1}
           ,{2},'{3}');update PeiXun set YuPiao = YuPiao - {1} where id = {0};
UPDATE dbo.PeiXun SET Status = 1 WHERE YuPiao = 0 AND ID = {0}", item.PeiXunID, item.DingPiaoShu, item.OrgID, item.FaFangFangShi);
                }
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                return true;
            }
            else
            {
                string sql = "";
                string chasql = string.Format(@"SELECT ID FROM dbo.PeiXunDetail WHERE PeiXunID = {0} AND OrgID = 0 AND OrgName = '{1}'", item.PeiXunID, item.OrgName);
                DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(chasql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    sql = string.Format(@"UPDATE PeiXunDetail
   SET [DingPiaoShu] = DingPiaoShu + {1},FaFangFangShi = '{3}'
 WHERE id = {2};update PeiXun set YuPiao = CASE WHEN YuPiao - {1} <= 0 THEN 0 ELSE YuPiao - {1}  END where id = {0};
UPDATE dbo.PeiXun SET Status = 1 WHERE YuPiao = 0 AND ID = {0}", item.PeiXunID, item.DingPiaoShu, Convert.ToInt32(ds.Tables[0].Rows[0][0]), item.FaFangFangShi);

                }
                else
                {
                    sql = string.Format(@"INSERT INTO PeiXunDetail
           ([PeiXunID]
           ,[DingPiaoShu]
           ,[OrgID],[OrgName],FaFangFangShi)
     VALUES
           ({0}
           ,{1}
           ,{2},'{3}','{4}');update PeiXun set YuPiao = YuPiao - {1} where id = {0};
UPDATE dbo.PeiXun SET Status = 1 WHERE YuPiao = 0 AND ID = {0}", item.PeiXunID, item.DingPiaoShu, item.OrgID, item.OrgName, item.FaFangFangShi);
                }
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                return true;
            }
        }

        public bool UpdatePeiXunDetailModel(PeiXunDetailModel item, int oldpiaoshu)
        {
            string sql = string.Format(@"UPDATE PeiXunDetail
   SET [PeiXunID] = {0}
      ,[DingPiaoShu] = {1}
      ,[OrgID] = {2},FaFangFangShi = '{5}'
 WHERE id = {3};update PeiXun set YuPiao = YuPiao + {4} - {1} where id = {0}", item.PeiXunID, item.DingPiaoShu, item.OrgID, item.ID, oldpiaoshu, item.FaFangFangShi);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool AddPeiXunModel(PeiXunModel item)
        {
            string sql = string.Format(@"INSERT INTO PeiXun
           ([Name]
           ,[ZhuJiangRen]
           ,[JiangZuoShiJijan]
           ,[JiangZuoDiDian]
           ,[PiaoShu]
           ,[StartTime]
           ,[EndTime]
           ,[YuPiao]
           ,[Status],JiangZuoType,XianZhiPiaoShu)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,{4}
           ,'{5}'
           ,'{6}'
           ,{7}
           ,{8},'{9}',{10})", item.Name, item.ZhuJiangRen, item.JiangZuoShiJijan, item.JiangZuoDiDian, item.PiaoShu, item.StartTime, item.EndTime, item.YuPiao, item.Status, item.JiangZuoType, item.XianZhiPiaoShu);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool UpdatePeiXunModel(PeiXunModel item)
        {
            string sql = string.Format(@"UPDATE PeiXun
   SET [Name] = '{0}'
      ,[ZhuJiangRen] = '{1}'
      ,[JiangZuoShiJijan] = '{2}'
      ,[JiangZuoDiDian] = '{3}'
      ,[PiaoShu] = {4}
      ,[StartTime] = '{5}'
      ,[EndTime] = '{6}'
      ,[YuPiao] = {10}
      ,[Status] = {7},JiangZuoType = '{9}',XianZhiPiaoShu = {11}
 WHERE id = {8}", item.Name, item.ZhuJiangRen, item.JiangZuoShiJijan, item.JiangZuoDiDian, item.PiaoShu, item.StartTime, item.EndTime, item.Status, item.ID, item.JiangZuoType, item.YuPiao, item.XianZhiPiaoShu);
            if(item.YuPiao <= 0 )
            {
                sql += " ;update peixun set Status = 1 where ID = " + item.ID;
            }
            if (item.EndTime < DateTime.Today)
            {
                sql += " ;update peixun set Status = 1 where ID = " + item.ID;
            }
            if(item.YuPiao >0 && item.EndTime > DateTime.Today)
            {
                sql += " ;update peixun set Status = 0 where ID = " + item.ID;
            }
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool DeletePeiXunDetailModel(PeiXunDetailModel item)
        {
            string sql = string.Format(@"DELETE FROM dbo.PeiXunDetail WHERE ID = {2};update PeiXun set YuPiao = YuPiao + {1} where id = {0}", item.PeiXunID, item.DingPiaoShu, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool DeletePeiXunModel(PeiXunModel item)
        {
            string sql = string.Format(@"DELETE FROM dbo.PeiXun WHERE ID = {0};DELETE FROM dbo.PeiXunDetail where PeiXunID = {0}", item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public int ShiFouChaoGuoPiaoShu(int peixunid, int orgid)
        {
            string sql = "";
            sql = string.Format(@"SELECT CASE WHEN SUM(DingPiaoShu) IS NULL THEN 0 ELSE SUM(DingPiaoShu) END dingpiaoshu FROM dbo.PeiXunDetail WHERE PeiXunID = {0} AND OrgID = {1}", peixunid, orgid);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            else return 0;
        }

        public bool AddPeiXunEmpDetailModel(ObservableCollection<PeiXunEmpDetailModel> item)
        {
            if (item != null && item.Count > 0)
            {
                string delete = string.Format("delete from PeiXunEmpDetail where orgid = {0} and PeiXunID = {1}", item[0].OrgID, item[0].PeiXunID);
                if (!string.IsNullOrEmpty(delete))
                    AccessHelper.GetInstance().ExecuteNonQuery(delete);
                foreach (PeiXunEmpDetailModel i in item)
                {
                    string sql = string.Format(@"INSERT INTO PeiXunEmpDetail
           ([EmpID]
           ,[PeiXunID]
           ,[Memo],QiTaZhang,orgid)
     VALUES
           ({0}
           ,{1}
           ,'{2}',{3},{4})", i.EmpID, i.PeiXunID, i.Memo, i.QiTaZhang, i.OrgID);
                    if (!string.IsNullOrEmpty(sql))
                        AccessHelper.GetInstance().ExecuteNonQuery(sql);
                }
                return true;
            }
            return true;
        }

        public ObservableCollection<PeiXunEmpDetailModel> PeiXunEmp(int peixunid, string orgids)
        {
            ObservableCollection<PeiXunEmpDetailModel> rights = new ObservableCollection<PeiXunEmpDetailModel>();
            string sql = "";
            sql = string.Format(@"SELECT row_number()over(order by o.Name,p.Name) Sec, o.Name OrgName,emp.Name EmpName,p.Name PeiXunName,o.id OrgID
    ,p.ZhuJiangRen,p.JiangZuoShiJijan,p.JiangZuoDiDian,p.JiangZuoType,ped.*
 FROM dbo.PeiXun p INNER JOIN dbo.PeiXunDetail pd ON p.ID = pd.PeiXunID
INNER JOIN dbo.PeiXunEmpDetail ped ON ped.peixunid = p.ID
INNER JOIN dbo.OrgForHR o ON o.ID = pd.OrgID
INNER JOIN dbo.Emp_BasicInfo emp ON emp.ID = ped.empid AND o.ID = emp.Orgid
WHERE 1=1 ");
            if (orgids != "0") sql += " and ped.orgid in ( " + orgids + ")";
            if (peixunid != 0) sql += " and p.id = " + peixunid;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                if (ds.Tables[0].Rows[0]["EmpID"].ToString() == "0")
                {
                    rights.Add(new PeiXunEmpDetailModel
                    {
                        EmpName = "其他",
                        OrgName = ds.Tables[0].Rows[0]["OrgName"].ToString(),
                        PeiXunName = ds.Tables[0].Rows[0]["PeiXunName"].ToString(),
                        EmpID = Convert.ToInt32(ds.Tables[0].Rows[0]["EmpID"]),
                        ID = Convert.ToInt32(ds.Tables[0].Rows[0]["ID"]),
                        PeiXunID = Convert.ToInt32(ds.Tables[0].Rows[0]["PeiXunID"]),
                        QiTaZhang = Convert.ToInt32(ds.Tables[0].Rows[0]["QiTaZhang"]),
                        Memo = ds.Tables[0].Rows[0]["Memo"].ToString()
                    });
                }
                rights.Add(PeiXunEmpDetailModel.FillEntity(dr));
            }
            return rights;
        }

        public bool UpdatePeiXunDetailFaFang(int id)
        {
            string sql = string.Format(@"UPDATE PeiXunDetail set YiFaFang = 1,YiFaFangPiaoShu = DingPiaoShu
 WHERE id = {0}", id);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
    }
}
